package com.weishaoying.mongo.dao;

import com.mongodb.BasicDBObject;
import com.weishaoying.mongo.entity.PT;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationResults;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.stereotype.Repository;

/**
 * MongoTemplate 聚合操作 - CSDN博客  https://blog.csdn.net/ruoguan_jishou/article/details/79289369
 *
 * @author WeiShaoying
 * @date 2018/5/18
 */
@Repository
public class StudentDao {

    @Autowired
    private MongoTemplate mongoTemplate;

    public void saveStudent(PT pt) {
        mongoTemplate.save(pt);
    }


    //Q1: 统计各个年级人数
    //报错Caused by: com.mongodb.MongoCommandException: Command failed with error 9: 'The 'cursor' option is required, except for aggregate with the explain argument' on server 192.168.111.102:27017. The full response is { "ok" : 0.0, "errmsg" : "The 'cursor' option is required, except for aggregate with the explain argument", "code" : 9, "codeName" : "FailedToParse" }
    //版本改成1.5.10.RELEASE即可
    public void aggeByGrade() {
        Aggregation aggregation = Aggregation.newAggregation(Aggregation.group("grade_name").count().as("总人数"));
        //返回的是一个集合
        AggregationResults<BasicDBObject> aggregationResults = mongoTemplate.aggregate(aggregation, "pt", BasicDBObject.class);
        for (BasicDBObject obj : aggregationResults) {
            System.out.println(obj.toString());
        }
    }
    /*
    { "_id" : "二年级" , "总人数" : 150}
    { "_id" : "一年级" , "总人数" : 50}
     */


    //Q2："统计某个年级某一项测试在某个范围的人数" 只需要匹配 年级+测试项目+项目分数 between 分数1 and 分数2 然后根据年级分组统计
    public void aggregation2() {
        Aggregation aggregation = Aggregation.newAggregation(
                Aggregation.unwind("items"),//解开，展开
                Aggregation.match(Criteria.where("items.item_name").is("俯卧撑").and("items.score").gt(20).lt(50)),
                Aggregation.group("grade_name").count().as("俯卧撑合格的人数"));
        AggregationResults<BasicDBObject> results = mongoTemplate.aggregate(aggregation, "pt", BasicDBObject.class);

        for (BasicDBObject obj : results) {
            System.out.println(obj.toString());
        }
    }
    //结果：
    //{ "_id" : "二年级" , "俯卧撑合格的人数" : 85}
    //{ "_id" : "一年级" , "俯卧撑合格的人数" : 30}


    //Q3："统计某个年级某一项测试不在某个范围的人数"  这个和Q2的区别在于他是not between and，这我们需要使用or来出来，java中对应的是orOperator；
    public void aggregation3() {
        Aggregation aggregation3 = Aggregation.newAggregation(
                Aggregation.match(Criteria.where("grade_name").is("二年级")),
                Aggregation.unwind("items"),
                Aggregation.match(Criteria.where("items.item_name").is("中长跑1000米").orOperator(
                        Criteria.where("items.score").lte(130),
                        Criteria.where("items.score").gte(200))),
                Aggregation.group("grade_name").count().as("中长跑不合格的人数")
        );

        AggregationResults<BasicDBObject> outputTypeCount3 = mongoTemplate.aggregate(aggregation3, "pt", BasicDBObject.class);
        for (BasicDBObject obj : outputTypeCount3) {
            System.out.println(obj);
        }
    }
    //{ "_id" : "二年级" , "中长跑不合格的人数" : 99}


    //Q4.“统计各个测试项目得分的最大值，最小值，平均值” 这个问题其实就是针对测试项目进行分组，然后使用 min max avg函数
    public void aggregation4() {
        Aggregation aggregation5 = Aggregation.newAggregation(
                Aggregation.match(Criteria.where("grade_name").is("二年级")),
                Aggregation.unwind("items"),
                Aggregation.group("$items.item_name")//注意$items表示变量,而不是具体的某个项目
                        .avg("$items.score").as("平均分")
                        .min("$items.score").as("最小值")
                        .max("$items.score").as("最大值"));

        AggregationResults<BasicDBObject> results = mongoTemplate.aggregate(aggregation5, "pt", BasicDBObject.class);

        for (BasicDBObject obj : results) {
            System.out.println(obj);
        }
    }
    //结果：
    /*
    { "_id" : "俯卧撑" , "平均分" : 22.706666666666667 , "最小值" : 2.0 , "最大值" : 41.0}
    { "_id" : "中长跑1000米" , "平均分" : 204.02333333333334 , "最小值" : 100.55 , "最大值" : 297.55}
    { "_id" : "短跑100米" , "平均分" : 15.079333333333334 , "最小值" : 10.546 , "最大值" : 19.546}
     */
}
